﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class BrandCategoryManager:ConnectionManager
    {
        #region Add/Update
        public SqlException AddBrandCategory(string _BrandID, string _CatCode, string _CatName)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO M_BRND_CT (BrandID, CatgryCode, CatgryName) VALUES (@BrandID, @CatgryCode, @CatgryName)";
                oCommand.Parameters.Add(new SqlParameter("@BrandID", _BrandID));
                oCommand.Parameters.Add(new SqlParameter("@CatgryCode", _CatCode));
                oCommand.Parameters.Add(new SqlParameter("@CatgryName", _CatName));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateBrandCategory(string _BrandID, string _CatCode, string _CatName)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "update M_BRND_CT set BrandID=@BrandID, CatgryName=@CatgryName WHERE CatgryCode=@CatgryCode";
                oCommand.Parameters.Add(new SqlParameter("@BrandID", _BrandID));
                oCommand.Parameters.Add(new SqlParameter("@CatgryCode", _CatCode));
                oCommand.Parameters.Add(new SqlParameter("@CatgryName", _CatName));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        #region Others

        public bool IsCategoryExists(string sCatgryCode)
        {
            DataSet ds = new DataSet();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_BRND_CT WHERE CatgryCode=@CatgryCode";
            oCommand.Parameters.Add(new SqlParameter("@CatgryCode", sCatgryCode));
        

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataTable GetBrandByCategoryName(string BrandID, string sCatgryName)
        {
            DataSet ds = new DataSet();
            SqlCommand oCommand = new SqlCommand();
            sCatgryName = '%' + sCatgryName + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_BRND_CT WHERE BrandID=@BrandID and CatgryName LIKE @CatgryName";
            oCommand.Parameters.Add(new SqlParameter("@CatgryName", sCatgryName));
            oCommand.Parameters.Add(new SqlParameter("@BrandID", BrandID));


            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        
        public DataTable GetCategoryByBrandID(string BrandID)
        {
            DataSet ds = new DataSet();
            SqlCommand oCommand = new SqlCommand();
         
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_BRND_CT WHERE BrandID=@BrandID";
            oCommand.Parameters.Add(new SqlParameter("@BrandID", BrandID));

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetBrandCategory(string sCatgryCode)
        {
            DataSet ds = new DataSet();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_BRND_CT WHERE CatgryCode=@CatgryCode";
            oCommand.Parameters.Add(new SqlParameter("@CatgryCode", sCatgryCode));


            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion


    }
}
